﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using KPIS.DBM;
using KPIS.GERP.GFMS.IDAL;
using KPIS.GERP.GFMS.MODEL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_CodeGenerate : IDAL_CodeGenerate
    {
        private DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public string GetNextCode(TransectionType type, string strBgmSourceNo)
        {
            int buddishtYear = new ThaiBuddhistCalendar().GetYear(DateTime.Now);
            string strCodeDate = buddishtYear.ToString("0000").Substring(DateTime.Today.Year.ToString("0000").Length - 2)
                + DateTime.Today.Month.ToString("00");
            string strResult = "";
            string strPrefix = "";
            string strMaxCode = "";

            switch (type)
            {
                case TransectionType.FimMoneyRequestBorrowNo:
                    strPrefix = "BR";
                    strMaxCode = FimMoneyRequestBorrowNo().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;
                case TransectionType.FimMoneyRequestRefNo:
                    strPrefix = "RQ";
                    strMaxCode = FimMoneyRequestRefNo().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;
                case TransectionType.FimMoneyReturnsRefNo:
                    strPrefix = "CL";
                    strMaxCode = FimMoneyReturnsRefNo().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;
                case TransectionType.FimTransfersRefNo:
                    strPrefix = "TF";
                    strMaxCode = FimTransfersRefNo().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;
                case TransectionType.FimPetitionRefNo:
                    strPrefix = "PE";
                    strMaxCode = FimPetitionRefNo().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;
                    /*
                    PIP
                        RV
                    */

                case TransectionType.FimReceiptNo:
                    strPrefix = "RC";
                    strMaxCode = FimReceiptNo().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;

                case TransectionType.FimPaymentsRefNo:
                    strPrefix = "PQ";
                    strMaxCode = FimPaymentsRefNo().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;
                case TransectionType.FimReceivesReceiptNo:
                    strPrefix = "No";
                    strMaxCode = FimReceivesReceiptNo().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;
                case TransectionType.IcmDeliveryNoteCode:
                    strPrefix = "DN";
                    strMaxCode = IcmDeliveryNoteCode().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;
                case TransectionType.FimPayrollRefNo:
                    strPrefix = "PL";
                    strMaxCode = FimPayroll().Where(m => (m.ReferenceNumber ?? "").StartsWith(strPrefix + strBgmSourceNo + strCodeDate)).Max(m => m.ReferenceNumber);
                    break;
            }

            // ผลลัพธ์ PV-5610-0014 คือ รหัสย่อของเรื่องที่เกี่ยวข้อง - ปี(พศ. 2 ตัว), เดือน(2 ตัว) - รันนิ่งนัมเบอร์ Padding 4 Digit

            if (string.IsNullOrEmpty(strMaxCode))
            {
                strResult = strPrefix + strBgmSourceNo + strCodeDate + "0001";
            }
            else
            {
                var strRunning = strMaxCode.Substring(strMaxCode.Length - 4);
                int nRunning;
                if (int.TryParse(strRunning, out nRunning))
                {
                    strResult = strPrefix + strBgmSourceNo + strCodeDate + (nRunning + 1).ToString("0000");
                }
                else
                {
                    strResult = strPrefix + strBgmSourceNo + strCodeDate + "0001";
                }
            }
            if(string.IsNullOrEmpty(strResult))
            {
                throw new Exception("Generated get null " + SystemInfo._DataProvider.ToString() + ", ");
            }
            return strResult;
        }

        public class AutogenerateNumberInfo
        {
            public int Seq { get; set; }
            public string ReferenceNumber { get; set; }
        }

        public IList<AutogenerateNumberInfo> FimMoneyRequestBorrowNo()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select money_req_seq, ref_no, borrow_no from fim_money_requests where record_status != @delete_status; ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["money_req_seq"]);
                        info.ReferenceNumber = drc[i]["borrow_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<AutogenerateNumberInfo> FimMoneyRequestRefNo()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select money_req_seq, ref_no, borrow_no from fim_money_requests where record_status != @delete_status; ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["money_req_seq"]);
                        info.ReferenceNumber = drc[i]["ref_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<AutogenerateNumberInfo> FimMoneyReturnsRefNo()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select return_seq, ref_no from fim_money_returns where record_status != @delete_status; ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["return_seq"]);
                        info.ReferenceNumber = drc[i]["ref_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<AutogenerateNumberInfo> FimTransfersRefNo()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select transfer_seq, ref_no from fim_transfers where record_status NOT Like @delete_status; ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["transfer_seq"]);
                        info.ReferenceNumber = drc[i]["ref_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        
        public IList<AutogenerateNumberInfo> FimPetitionRefNo()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select petition_seq, ref_no from fim_petitions where record_status NOT Like @delete_status";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["petition_seq"]);
                        info.ReferenceNumber = drc[i]["ref_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        
        public IList<AutogenerateNumberInfo> FimPaymentsRefNo()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select pay_seq, ref_no from fim_payments where record_status NOT Like @delete_status; ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["pay_seq"]);
                        info.ReferenceNumber = drc[i]["ref_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        
        public IList<AutogenerateNumberInfo> FimReceivesReceiptNo()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select receive_seq, receipt_no from fim_receives where record_status NOT Like @delete_status ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["receive_seq"]);
                        info.ReferenceNumber = drc[i]["receipt_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<AutogenerateNumberInfo> FimReceiptNo()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select receive_seq, ref_no from fim_receives/* where record_status NOT Like @delete_status*/";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["receive_seq"]);
                        info.ReferenceNumber = drc[i]["ref_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        
        
        public IList<AutogenerateNumberInfo> IcmDeliveryNoteCode()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select DnId, Code from IcmDeliveryNote where RecordStatus = @delete_status; ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["DnId"]);
                        info.ReferenceNumber = drc[i]["Code"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<AutogenerateNumberInfo> FimPayroll()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<AutogenerateNumberInfo> infoList = new List<AutogenerateNumberInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select payroll_seq, ref_no from fim_payroll where record_status != @delete_status; ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        AutogenerateNumberInfo info = new AutogenerateNumberInfo();
                        info.Seq = Convert.ToInt32(drc[i]["payroll_seq"]);
                        info.ReferenceNumber = drc[i]["ref_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
    }
}